package com.easyui.DB;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


public class OperateDB {
	private static List<Object> paramList = null;//参数链表

	public OperateDB() {}
	
	/**
	 * 返回参数链表
	 * @return
	 */
	public static List<Object> getParamList() {
System.out.println("返回参数链表\n");
		return paramList;
	}
	
	/**
	 * 设置参数链表
	 * @param list
	 */
	public static void setParmList(List<Object> list) {
System.out.println("设置参数链表\n");
		paramList = list;
	}
	
	/**
	 * 查询数据
	 * 返回操作结果
	 * @param sql
	 * @return
	 */
	public static List<Map> excuteQuery(String sql) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<Map> list = null;
		
		try {
			conn = ConnectDB.getConnection();
			pstmt = conn.prepareStatement(sql);
			
			if(paramList != null && paramList.size() > 0) {//参数链表有数据，即带参数的查询
				for(int i=0; i<paramList.size(); i++) {
					pstmt.setObject(i+1, paramList.get(i));
				}
			}
			
			rs = pstmt.executeQuery();

			if(rs != null) {
				list = new ArrayList<Map>();
				ResultSetMetaData rsmd = rs.getMetaData();
				
				while(rs.next()) {
					Map<String, Object> map = new HashMap<String, Object>();
					int count = rsmd.getColumnCount();
					
					for(int i =1; i<= count; i++) {
						String name = rsmd.getColumnName(i);
						Object value = rs.getObject(i);
						map.put(name, value);
					}
					list.add(map);
				}
			}
System.out.println("查询数据成功");
		} catch (SQLException e) {
			System.out.println("查询数据出错\n");
			e.printStackTrace();
		} finally {
			close(pstmt);
			close(rs);
			close(conn);
		}

		return list;
	}
	
	public static List<Map> excuteQuery(Connection conn, String sql) {
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<Map> list = null;
		
		try {
			conn = ConnectDB.getConnection();
			pstmt = conn.prepareStatement(sql);
			
			if(paramList != null && paramList.size() > 0) {//参数链表有数据，即带参数的查询
				for(int i=0; i<paramList.size(); i++) {
					pstmt.setObject(i+1, paramList.get(i));
				}
			}
			
			rs = pstmt.executeQuery();

			if(rs != null) {
				list = new ArrayList<Map>();
				ResultSetMetaData rsmd = rs.getMetaData();
				
				while(rs.next()) {
					Map<String, Object> map = new HashMap<String, Object>();
					int count = rsmd.getColumnCount();
					
					for(int i =1; i<= count; i++) {
						String name = rsmd.getColumnName(i);
						Object value = rs.getObject(i);
						map.put(name, value);
					}
					list.add(map);
				}
			}
System.out.println("查询数据成功");
		} catch (SQLException e) {
			System.out.println("查询数据出错\n");
			e.printStackTrace();
		} finally {
			close(pstmt);
			close(rs);
		}

		return list;
	}
	
	
	/**
	 * 更新(修改/插入/删除)数据
	 * 返回操作结果
	 * @param sql
	 * @return
	 */
	public static boolean update(String sql) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		boolean isSuccess = false;
		
		try {
			conn = ConnectDB.getConnection();
			pstmt = conn.prepareStatement(sql);
			
			if(paramList != null && paramList.size() > 0) {//参数链表有数据，即带参数的更新或删除
				for(int i=0; i<paramList.size(); i++) {
					pstmt.setObject(i+1, paramList.get(i));
					System.out.println(paramList.get(i));
				}
			}
			
			int i = pstmt.executeUpdate();
			if(i>= 0) {
				isSuccess = true;
System.out.println("修改或插入数据成功");
			} else {
				isSuccess = false;
System.out.println("修改或插入失败");
			}
		} catch (SQLException e) {
			System.out.println("修改或插入数据出错，详情如下：\n");
			e.printStackTrace();
		} finally {
			close(pstmt);
			close(conn);
		}
		
		return isSuccess;
	}
	
	
	/**
	 * 更新(删除/插入/修改)数据
	 * 用于批量更新情况
	 * @param conn
	 * @param sql
	 * @return
	 */
	public static boolean update(Connection conn, String sql) {
		PreparedStatement pstmt = null;
		boolean isSuccess = false;
		
		try {
			pstmt = conn.prepareStatement(sql);
			if(paramList != null && paramList.size() > 0) {
				for(int i=0; i<paramList.size(); i++) {
					pstmt.setObject(i+1, paramList.get(i));
				}
			} else {
				return false;
			}
			
			int i = pstmt.executeUpdate();
			System.out.println(i);
			if(i>= 0) {
				isSuccess = true;
System.out.println("更新数据成功\n");
			} else {
				isSuccess = false;
System.out.println("更新数据失败\n");
			}
		} catch (SQLException e) {
			System.out.println("更新数据出错，详情如下：\n");
			e.printStackTrace();
		} finally {
			close(pstmt);
		}
		
		return isSuccess;
	}
	
	
	/**
	 * 插入数据并获取自动生成的键值
	 * 返回操作结果以及自动生成的键值
	 * @param sql
	 * @return
	 */
	public static Map<String, Object> insertAndGet(String sql) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Map<String, Object> map = null;
		boolean isSuccess = false;
		int generatedKey = -1;
		
		try {
			conn = ConnectDB.getConnection();
			pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			map = new HashMap<String, Object>();
			
			if(paramList != null && paramList.size() > 0) { //参数链表有数据
				for(int i=0; i<paramList.size(); i++) {
					pstmt.setObject(i+1, paramList.get(i));
				}
				
				int i = pstmt.executeUpdate();
				if(i>= 0) {
					isSuccess = true;
					rs = pstmt.getGeneratedKeys();
					
					if(rs != null && rs.next()) {
						generatedKey = rs.getInt(1);
					} else {
						generatedKey = -1;
					}
				} else {
					isSuccess = false;
					generatedKey = -1;
				}
			} else {//参数链表没有数据则查询出错
				isSuccess = false;
				generatedKey = -1;
			}
			
			map.put("isSuccess", isSuccess);
			map.put("generatedKey", generatedKey);
		} catch (SQLException e) {
			System.out.println("插入数据出错，详情如下：\n");
			e.printStackTrace();
		} finally {
			close(rs);
			close(pstmt);
			close(conn);
		}
		
		return map;
	}
	
	/**
	 * 用于批量操作
	 * @param conn
	 * @param sql
	 * @return
	 */
	public static Map<String, Object> insertAndGet(Connection conn, String sql) {
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Map<String, Object> map = null;
		boolean isSuccess = false;
		int generatedKey = -1;
		
		try {
			pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			map = new HashMap<String, Object>();
			
			if(paramList != null && paramList.size() > 0) { //参数链表有数据
				for(int i=0; i<paramList.size(); i++) {
					pstmt.setObject(i+1, paramList.get(i));
				}
				
				int i = pstmt.executeUpdate();
				if(i>0) {
					isSuccess = true;
					rs = pstmt.getGeneratedKeys();
					
					if(rs != null && rs.next()) {
						generatedKey = rs.getInt(1);
					} else {
						generatedKey = -1;
					}
				} else {
					isSuccess = false;
					generatedKey = -1;
				}
			} else {//参数链表没有数据则查询出错
				isSuccess = false;
				generatedKey = -1;
			}
			
			map.put("isSuccess", isSuccess);
			map.put("generatedKey", generatedKey);
		} catch (SQLException e) {
			System.out.println("插入数据出错，详情如下：\n");
			e.printStackTrace();
		} finally {
			close(rs);
			close(pstmt);
		}
		
		return map;
	}
	
	
	
	/**
	 * 关闭ResultSet
	 * @param rs
	 */
	public static void close(ResultSet rs) {
		if(rs != null) {
			try {
				rs.close();
System.out.println("关闭ResultSet成功！\n");
			} catch (SQLException e) {
				System.out.println("关闭ResultSet失败，详情如下：\n");
				e.printStackTrace();
			} finally {
				rs = null;
			}
		}
	}
	
	/**
	 * 关闭PreparedStatement
	 * @param pstmt
	 */
	public static void close(PreparedStatement pstmt) {
		if(pstmt != null) {
			try {
				pstmt.close();
System.out.println("关闭PreparedStatement成功！\n");
			} catch (SQLException e) {
				System.out.println("关闭PreparedStatement失败，详情如下：\n");
				e.printStackTrace();
			} finally {
				pstmt = null;
			}
		}
	}
	
	
	/**
	 * 关闭Connection
	 * @param conn
	 */
	public static void close(Connection conn) {
		if(conn != null) {
			try {
				conn.close();
System.out.println("关闭Connection成功！\n");
			} catch (SQLException e) {
				System.out.println("关闭Connection失败，详情如下：\n");
				e.printStackTrace();
			} finally {
				conn = null;
			}
		}
	}
	
}
